﻿/****************************** Module Header ******************************\
Module Name:    ExportSystemMessageCommand.cs
Project:        SQL Server Error Log Parsing module

This is the implementation of the Export-SystemMessage Cmdlet, which exports 
system messages from a SQL Server instance into a tab-delimited text file.

This source is subject to the Microsoft Public License.
See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
All other rights reserved.

THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/

using System;
using System.Data.SqlClient;
using System.IO;
using System.Management.Automation;
using System.Text;
using System.Text.RegularExpressions;

namespace SQLServerSleuth.PowerShell.Commands.ParseERRORLOG
{
    /// <summary>
    /// The implementation of the Export-SystemMessage Cmdlet.
    /// </summary>
    [Cmdlet("Export", "SystemMessage")]
    public class ExportSystemMessageCommand : PSCmdlet
    {
        #region Cmdlet parameters
        
        /// <summary>
        /// The path to the text file into which the system messages should 
        /// be written.
        /// </summary>
        [Parameter(Mandatory = true, Position = 0)]
        public string OutputFilePath
        {
            get
            {
                return this._outputFilePath;
            }

            set
            {
                this._outputFilePath = value.Trim();
            }
        }
        public string _outputFilePath = "";

        /// <summary>
        /// The name of the SQL Server instance from which the system 
        /// messages should be exported.
        /// </summary>
        [Parameter(Mandatory = true, Position = 1)]
        public string ServerName
        {
            get
            {
                return this._serverName;
            }

            set
            {
                this._serverName = value.Trim();
            }
        }
        public string _serverName = "";

        /// <summary>
        /// A switch that indicates Windows Authentication (SSPI) to access 
        /// the SQL Server instance.
        /// </summary>
        [Parameter(Mandatory = true, ParameterSetName = "WindowsAuthentication", Position = 2)]
        public System.Management.Automation.SwitchParameter TrustedConnection
        {
            get
            {
                return this._trustedConnection;
            }

            set
            {
                this._trustedConnection = value;
            }
        }
        public System.Management.Automation.SwitchParameter _trustedConnection;

        /// <summary>
        /// When using SQL Server Authentication, this is the name of the 
        /// login to use when connecting to the SQL Server instance.
        /// </summary>
        [Parameter(Mandatory = true, ParameterSetName = "SQLServerAuthentication", Position = 3)]
        public string UserName
        {
            get
            {
                return this._userName;
            }

            set
            {
                this._userName = value.Trim();
            }
        }
        public string _userName = "";

        /// <summary>
        /// When using SQL Server Authentication, this is the password of 
        /// the login to use when connecting to the SQL Server instance.
        /// </summary>
        [Parameter(Mandatory = true, ParameterSetName = "SQLServerAuthentication", Position = 4)]
        public string Password
        {
            get
            {
                return this._password;
            }

            set
            {
                this._password = value.Trim();
            }
        }
        public string _password = "";

        /// <summary>
        /// The IDs of messages that should be excluded from the export.
        /// </summary>
        [Parameter(Position = 5)]
        public int[] MessageIDsToExclude
        {
            get
            {
                return this._messageIDsToExclude;
            }

            set
            {
                this._messageIDsToExclude = value;
            }
        }
        public int[] _messageIDsToExclude = new int[0];

        /// <summary>
        /// The language of the system messages to export.
        /// </summary>
        [Parameter(Position = 6)]
        public short LanguageID
        {
            get
            {
                return this._languageID;
            }

            set
            {
                this._languageID = value;
            }
        }
        //  The LCID value is being cast to a sixteen bit integer to match 
        //  the size of the [language_id] column of the [sys].[messages] 
        //  catalog view.
        public short _languageID = (short)System.Globalization.CultureInfo.CurrentCulture.LCID;

        /// <summary>
        /// A switch that indicates that the target SQL Server instance is 
        /// running SQL Server 2000 (8.0).
        /// </summary>
        [Parameter(Position = 7)]
        public System.Management.Automation.SwitchParameter SQLServer2000
        {
            get
            {
                return this._SQLServer2000;
            }

            set
            {
                this._SQLServer2000 = value;
            }
        }
        public System.Management.Automation.SwitchParameter _SQLServer2000;

        /// <summary>
        /// The connection string will include the "Application Name" 
        /// keywords, which provides context to the SQL Server instance.  By 
        /// default, the application name defaults to the name of the 
        /// module, but this can be overridden.
        /// </summary>
        [Parameter(Position = 8)]
        public string ApplicationName
        {
            get
            {
                return this._applicationName;
            }

            set
            {
                this._applicationName = value.Trim();
            }
        }
        public string _applicationName = "SQL Server Error Log Parsing module";

        #endregion Cmdlet parameters

        #region Overridden methods from the System.Management.Automation.PSCmdlet class

        /// <summary>
        /// Connect to the specified SQL Server instance and exported the 
        /// desired system messages.
        /// </summary>
        protected override void ProcessRecord()
        {
            //  Write the values of the input parameters to the debug stream.
            //  NOTE: For security reasons, the password isn't written out 
            //  directly.  Instead, the hash code (using the GetHashCode() 
            //  method inherited from the System.Object class) is displayed.
            this.writeDebugOutput_CmdletParameters();

            string connectionString = "";
            StringBuilder output = new StringBuilder();
            string query = "";
            int systemMessageCounter = 0;

            //  Verify that the output directory already exists.
            this._outputDirectoryPath = this.getOutputDirectoryPath(this._outputFilePath);

            //  Generate the T-SQL query for retrieving the system messages.
            query = this.buildQuery(
                this._messageIDsToExclude,
                this._SQLServer2000
            );
            WriteDebug
            (
                string.Format
                (
                    "Query = \"{0}\"",
                    query
                )
            );

            //  Generate the connection string for retrieving the system 
            //  messages.
            connectionString = this.buildConnectionString
            (
                this._serverName,
                this._trustedConnection.IsPresent,
                this._userName,
                this._password,
                this._applicationName
            );
            WriteDebug
            (
                string.Format
                (
                    "Connection string = \"{0}\"",
                    Regex.Replace
                    (
                        connectionString,
                        ";Password=.*?;Application",
                        ";Password=__REDACTED__;Application"
                    )
                )
            );

            try
            {
                //  Execute the T-SQL query and add the results to the 
                //  output StringBuilder.
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        command.CommandType = System.Data.CommandType.Text;
                        command.CommandText = query;
                        using (SqlDataReader dataReader = command.ExecuteReader(System.Data.CommandBehavior.SingleResult))
                        {
                            while (dataReader.Read())
                            {
                                systemMessageCounter++;
                                output.AppendFormat
                                (
                                    "{0}\t{1}\t{2}\t{3}\r\n",
                                    dataReader.GetInt32(0),
                                    dataReader.GetByte(1),
                                    dataReader.GetByte(2),
                                    dataReader.GetString(3)
                                );
                            }
                        }
                    }
                }

                //  Write the output to file.
                System.IO.File.WriteAllText
                (
                    this._outputFilePath,
                    output.ToString(),
                    System.Text.Encoding.Unicode
                );

                //  Write a status message to the verbose stream.
                if (1 == systemMessageCounter)
                {
                    WriteVerbose
                    (
                        string.Format
                        (
                            "{0:N0} system message was exported to the \"{1}\" file.",
                            systemMessageCounter,
                            this._outputFilePath
                        )
                    );
                }
                else
                {
                    WriteVerbose
                    (
                        string.Format
                        (
                            "{0:N0} system messages were exported to the \"{1}\" file.",
                            systemMessageCounter,
                            this._outputFilePath
                        )
                    );
                }

                //  Write the name of the output file to the output pipeline.
                WriteObject(this._outputFilePath);
            }
            catch (SqlException exception)
            {
                ErrorRecord errorRecord = new ErrorRecord
                (
                    exception,
                    "QueryFailure",
                    ErrorCategory.OpenError,
                    "SQL Server instance"
                );
                ThrowTerminatingError(errorRecord);
            }
            catch (Exception exception)
            {
                ErrorRecord errorRecord = new ErrorRecord
                (
                    exception,
                    "ExportFailure",
                    ErrorCategory.NotSpecified,
                    "SQL Server instance"
                );
                ThrowTerminatingError(errorRecord);
            }

            return;
        }

        #endregion Overridden methods from the System.Management.Automation.PSCmdlet class

        #region Private class members

        private string _outputDirectoryPath = "";

        /// <summary>
        /// Generate the connection string for connecting to the target 
        /// SQL Server instance.
        /// </summary>
        /// <param name="serverName">The name of the target SQL Server 
        /// instance.</param>
        /// <param name="useTrustedConnection">A flag that indicates that 
        /// Windows Authentication (SSPI) should be used.</param>
        /// <param name="userName">The name of the login to use when 
        /// connecting to the SQL Server instance.</param>
        /// <param name="password">The password to use when connecting to 
        /// the SQL Server instance.</param>
        /// <param name="applicationName">The value to use for the 
        /// "Application Name" value of the connection string.</param>
        /// <returns>The connection string for querying the system messages.</returns>
        private string buildConnectionString
        (
            string serverName,
            bool useTrustedConnection,
            string userName,
            string password,
            string applicationName
        )
        {
            string connectionString = "";

            if (useTrustedConnection)
            {
                connectionString = string.Format
                (
                    "Server={0};Database=tempdb;Trusted_Connection=True;Application Name={1}",
                    serverName,
                    applicationName
                );
            }
            else
            {
                connectionString = string.Format
                (
                    "Server={0};Database=tempdb;User ID={1};Password={2};Application Name={3}",
                    serverName,
                    userName,
                    password,
                    applicationName
                );
            }

            return connectionString;
        }

        /// <summary>
        /// Generate the T-SQL query text that will be used to retrieve the 
        /// system messages from the target SQL Server instance.
        /// </summary>
        /// <param name="messageIDsToExclude">A list of message IDs that 
        /// should be excluded from the export output.</param>
        /// <param name="isForSQLServer2000">A flag that indicates that the 
        /// target SQL Server instance is running SQL Server 2000 (8.0).</param>
        /// <returns>The T-SQL query for retrieving the system messages.</returns>
        private string buildQuery
        (
            int[] messageIDsToExclude,
            bool isForSQLServer2000
        )
        {
            string query = "";

            if (messageIDsToExclude.Length > 0)
            {
                //  Convert the array of message ID integers to an array of 
                //  strings, in order to leverage the System.String.Join() 
                //  static method.  This is much simpler to do in 
                //  PowerShell, but this implementation detail shouldn't be 
                //  imposed on the Cmdlet user.
                string[] stringMessageIDsToExclude = Array.ConvertAll<int, string>(
                    messageIDsToExclude,
                    new Converter<int, string>(this.convertIntegerToString)
                );

                if (isForSQLServer2000)
                {
                    //  Since the target instance is SQL Server 2000, query 
                    //  the [master].[dbo].[sysmessages] system table.
                    query = string.Format
                    (
                        "SET NOCOUNT ON; SELECT [error] AS [MessageID], [severity] AS [Severity], Cast((([dlevel] & 0x80) / 0x80) AS TinyInt) AS [IsLogged], [description] AS [SystemMessage] FROM [master].[dbo].[sysmessages] WHERE (([mslangid] = {0}) AND ([error] NOT IN ({1})));",
                        this._languageID,
                        string.Join
                        (
                            ", ",
                            stringMessageIDsToExclude
                        )
                    );
                }
                else
                {
                    //  Since the target instance is SQL Server 2005 or 
                    //  later, query the [sys].[messages] catalog view.
                    query = string.Format
                    (
                        "SET NOCOUNT ON; SET NOCOUNT ON; SELECT [message_id] AS [MessageID], [severity] AS [Severity], Cast([is_event_logged] AS TinyInt) AS [IsLogged], [text] AS [SystemMessage] FROM [sys].[messages] WHERE (([language_id] = {0}) AND ([message_id] NOT IN ({1})));",
                        this._languageID,
                        string.Join
                        (
                            ", ",
                            stringMessageIDsToExclude
                        )
                    );
                }
            }
            else
            {
                if (isForSQLServer2000)
                {
                    //  Since the target instance is SQL Server 2000, query 
                    //  the [master].[dbo].[sysmessages] system table.
                    query = string.Format
                    (
                        "SET NOCOUNT ON; SELECT [error] AS [MessageID], [severity] AS [Severity], Cast((([dlevel] & 0x80) / 0x80) AS TinyInt) AS [IsLogged], [description] AS [SystemMessage] FROM [master].[dbo].[sysmessages] WHERE ([mslangid] = {0});",
                        this._languageID
                    );
                }
                else
                {
                    //  Since the target instance is SQL Server 2005 or 
                    //  later, query the [sys].[messages] catalog view.
                    query = string.Format
                    (
                        "SET NOCOUNT ON; SET NOCOUNT ON; SELECT [message_id] AS [MessageID], [severity] AS [Severity], Cast([is_event_logged] AS TinyInt) AS [IsLogged], [text] AS [SystemMessage] FROM [sys].[messages] WHERE ([language_id] = {0});",
                        this._languageID
                    );
                }
            }

            return query;
        }

        /// <summary>
        /// Converts an element of an integer array to an element of a 
        /// string array.
        /// </summary>
        /// <param name="integerValue">The value of the array element.</param>
        /// <returns>The string representation of the array element value.</returns>
        private string convertIntegerToString(int integerValue)
        {
            return integerValue.ToString();
        }

        /// <summary>
        /// Extract the directory path from the output file path and insure 
        /// that it corresponds to an existing directory.
        /// </summary>
        /// <param name="outputFilePath">The path to the output file for the Cmdlet.</param>
        /// <returns>The path to the directory into which the output file 
        /// should be written.</returns>
        private string getOutputDirectoryPath(string outputFilePath)
        {
            string outputDirectoryPath = "";

            //  Verify that the given output file path doesn't refer to an 
            //  existing directory (which is unusual but possible).
            if (Directory.Exists(outputFilePath))
            {
                ErrorRecord errorRecord = new ErrorRecord
                (
                    new InvalidOperationException
                    (
                        string.Format
                        (
                            "The specified output file path \"{0}\" corresponds to an existing directory.",
                            outputFilePath
                        )
                    ),
                    "InvalidOutputFilePath",
                    ErrorCategory.InvalidArgument,
                    "System messages output file"
                );
                ThrowTerminatingError(errorRecord);
            }

            //  Extract the path to the parent directory.
            outputDirectoryPath = Path.GetDirectoryName(outputFilePath);

            //  Verify that the parent directory already exists.
            if (!Directory.Exists(outputDirectoryPath))
            {
                ErrorRecord errorRecord = new ErrorRecord
                (
                    new System.IO.DirectoryNotFoundException("outputDirectoryPath"),
                    "InvalidOutputFilePath",
                    ErrorCategory.ObjectNotFound,
                    "System messages output file"
                );
                ThrowTerminatingError(errorRecord);
            }

            return outputDirectoryPath;
        }

        /// <summary>
        /// Write the values of all relevant Cmdlet parameters and arguments 
        /// to the debug stream.
        /// </summary>
        private void writeDebugOutput_CmdletParameters()
        {
            WriteDebug
            (
                string.Format
                (
                    "Parameter set = {0}",
                    this.ParameterSetName
                )
            );
            WriteDebug
            (
                string.Format
                (
                    "$OutputFilePath = \"{0}\"",
                    this._outputFilePath
                )
            );
            WriteDebug
            (
                string.Format
                (
                    "$ServerName = \"{0}\"",
                    this._serverName
                )
            );
            WriteDebug
            (
                string.Format
                (
                    "$TrustedConnection = {0}",
                    this._trustedConnection.IsPresent
                )
            );
            WriteDebug
            (
                string.Format
                (
                    "$UserName = \"{0}\"",
                    this._userName
                )
            );
            if (string.IsNullOrEmpty(this._password))
            {
                WriteDebug
                (
                    "$Password hash code = 0"
                );
            }
            else
            {
                WriteDebug
                (
                    string.Format
                    (
                        "$Password hash code = {0}",
                        this._password.GetHashCode()
                    )
                );
            }
            WriteDebug
            (
                string.Format
                (
                    "$SQLServer2000 = {0}",
                    this._SQLServer2000
                )
            );
            WriteDebug
            (
                string.Format
                (
                    "$LanguageID = {0}",
                    this._languageID
                )
            );

            return;
        }
        
        #endregion Private class members
    }
}
